﻿using HCQ2_Common.SQL;
using HCQ2_IDAL;
using HCQ2_Model.VRDD.InputDto._0602;
using HCQ2_Model.VRDD.OutputDto._0602;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HCQ2_DAL_MSSQL
{
    public partial class T_VRDDCircleUserServiceDAL: IT_VRDDCircleUserServiceDAL
    {
        /// <summary>
        ///  用户服务数据层接口
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public List<OutputSaveService> GetServiceList(InputSaveService param, decimal gps_x, decimal gps_y)
        {
            sb?.Clear();
            sb.Append($@"SELECT s.voice_url,s.voice_len,s.bs_type,s.issue_date,d.*,u.*,is_read=(SELECT CAST(COUNT(0) AS BIT) FROM T_VRDDCircleReadService WHERE du_id={param.user_id} AND s_id=(SELECT id FROM T_VRDDCircleBaseService WHERE s_type=s.bs_type)) FROM (SELECT du_id,voice_url,voice_len,
            bs_type,CONVERT(varchar(100),create_date,120) AS issue_date FROM T_VRDDCircleUserService WHERE 1=1 ");
            //发布时间
            if (param.bs_period > 0)
                sb.Append($" AND bs_period<={param.bs_period} ");
            //服务类别
            if (!string.IsNullOrEmpty(param.bs_type))
                sb.Append($" AND bs_type='{param.bs_type}' ");
            sb.Append($@")s INNER JOIN ");
            //获取未读
            if (param.is_read > 0)
                sb.Append($@"(SELECT s_type FROM T_VRDDCircleBaseService WHERE id NOT IN(
                SELECT s_id FROM T_VRDDCircleReadService WHERE du_id={param.user_id}))r ON s.bs_type=r.s_type INNER JOIN ");
            sb.Append($@"(SELECT du_id,du_name,hide_name,user_type,city,sense=dbo.GetDistance({gps_x},{gps_y},gps_x,gps_y)  
            FROM dbo.DD_User with(nolock) WHERE 1=1 ");
            //性别
            if (param.du_sex > 0)
                sb.Append($" AND du_sex={param.du_sex} ");
            //是否VIP【只听VIP】
            if (param.user_type > 0)
                sb.Append($" AND user_type={param.user_type} ");
            //距离
            if (param.distance > 0)
                sb.Append($@" AND gps_x BETWEEN 
                dbo.GetLngE({gps_x},{gps_y},{param.distance*1000}) AND dbo.GetLngW({gps_x},{gps_y},{param.distance * 1000}) AND gps_y 
                BETWEEN dbo.GetLatN({gps_y},{param.distance * 1000}) AND dbo.GetLatS({gps_y},{param.distance * 1000}) AND 
                dbo.GetDistance({gps_x},{gps_y},gps_x,gps_y)<={param.distance * 1000} ");
            sb.Append($@")d ON s.du_id=d.du_id INNER JOIN (SELECT user_id, user_name, user_img,isStar=(SELECT COUNT(0) FROM DD_Fans 
                WHERE (fan_id={param.user_id} AND du_id=tu.user_id) OR (du_id={param.user_id} AND fan_id=tu.user_id)) FROM dbo.T_User tu WHERE 1 = 1");
            //是否在线【只听在线】
            if (param.du_line > 0)
                sb.Append($" AND user_status=1 ");
            //关键字
            if (!string.IsNullOrEmpty(param.keyword))
                sb.Append($@" AND (user_name LIKE '%{param.keyword}%' OR user_phone LIKE '%{param.keyword}%') ");
            sb.Append(")u ON d.du_id=u.user_id ");
            //是否只返回好友【不听陌生人】
            if (param.user_contacts == 1)
                sb.Append($@" INNER JOIN (SELECT case when dd_id_1={param.user_id} THEN dd_id_2 else dd_id_1 end AS dd_id 
                FROM dbo.DD_1 with(nolock) WHERE dd_id_1={param.user_id} OR dd_id_2={param.user_id} )d1 ON d.du_id=d1.dd_id ");
            //排序类别
            switch (param.order_type)
            {
                //是否VIP
                case 1: sb.Append(" ORDER BY d.user_type DESC;"); break;
                //是否关注
                case 2: sb.Append(" ORDER BY u.isStar DESC;"); break;
                //距离
                case 3: sb.Append(" ORDER BY d.sense;"); break;
                //时间
                case 4: sb.Append(" ORDER BY s.issue_date DESC;"); break;
                default: sb.Append(" ORDER BY u.user_name;"); break;
            }
            return SqlHelper.ExecuteList<OutputSaveService>(sb.ToString());
        }
    }
}